-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE sp_search_prospect 
	-- Add the parameters for the stored procedure here
	@name varchar(50),
	@midname varchar(50) ,
	@surname varchar(50) ,
	@fecha varchar(20) ,
	@visitfecha varchar(20) ,
	@activity varchar(10),
	@saleid varchar(10),
	@salelever varchar(10)
AS
BEGIN
	IF(@salelever = '1')
	BEGIN
		SELECT * FROM (
						SELECT G.GuestID, G.Name,G.Middle, G.Surname, S.ScheduceCallID, REPLACE(ISNULL(CONVERT(VARCHAR(10),V.VisitCalendarID),'Call'),ISNULL(CONVERT(VARCHAR(10),V.VisitCalendarID),''),'Visit') AS Activity, ISNULL(CONVERT(VARCHAR(10),V.Datetime,101),'') AS VisitDate, ISNULL(CONVERT(VARCHAR(10),S.Date,101),'') AS Fercha
						FROM Guests G
						LEFT JOIN VisitCalendar V ON G.GuestID = V.GuestID
						LEFT JOIN ScheduleCall S ON G.GuestID = S.GuestID
						WHERE G.IsGuest = 0
						AND ((S.ScheduceCallID IS NOT NULL AND V.VisitCalendarID IS NULL ) OR (V.VisitCalendarID IS NOT NULL AND CONVERT(VARCHAR(10),V.Datetime,1) >= CONVERT(VARCHAR(10),GETDATE(),1)))
						AND G.SaleID = @saleid
						) AS TB
		WHERE TB.Name like '%'+@name+'%'
		AND TB.Middle like '%'+@midname+'%'
		AND TB.Surname like '%'+@surname+'%'
		AND TB.Fercha  like '%'+@fecha+'%'
		AND TB.VisitDate  like '%'+@visitfecha+'%'
		AND TB.Activity like '%'+@activity+'%'
	END
	ELSE
	BEGIN
		SELECT * FROM (
						SELECT G.GuestID, G.Name,G.Middle, G.Surname, S.ScheduceCallID, REPLACE(ISNULL(CONVERT(VARCHAR(10),V.VisitCalendarID),'Call'),ISNULL(CONVERT(VARCHAR(10),V.VisitCalendarID),''),'Visit') AS Activity, ISNULL(CONVERT(VARCHAR(10),V.Datetime,101),'') AS VisitDate, ISNULL(CONVERT(VARCHAR(10),S.Date,101),'') AS Fercha
						FROM Guests G
						LEFT JOIN VisitCalendar V ON G.GuestID = V.GuestID
						LEFT JOIN ScheduleCall S ON G.GuestID = S.GuestID
						WHERE G.IsGuest = 0
						AND ((S.ScheduceCallID IS NOT NULL AND V.VisitCalendarID IS NULL ) OR (V.VisitCalendarID IS NOT NULL AND CONVERT(VARCHAR(10),V.Datetime,1) >= CONVERT(VARCHAR(10),GETDATE(),1)))						
						) AS TB
		WHERE TB.Name like '%'+@name+'%'
		AND TB.Middle like '%'+@midname+'%'
		AND TB.Surname like '%'+@surname+'%'
		AND TB.Fercha  like '%'+@fecha+'%'
		AND TB.VisitDate  like '%'+@visitfecha+'%'
		AND TB.Activity like '%'+@activity+'%'
	END
END
GO
